package persistance;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import modele.Client;
import modele.Colis;
import modele.Entrepot;
import modele.Expedition;
import modele.Fidele;
import modele.Mission;
import modele.Personne;

public class PersistanceEntreprot {
	
 
PreparedStatement st=null;

private Connection connection;
private Statement stmt;
private String JDBC_DRIVER;
private String DB_URL;
private String USER = "root";
private String PASS = "";

	public PersistanceEntreprot(){
		this.connection = null;
		this.stmt = null;
		this.JDBC_DRIVER = "com.mysql.jdbc.Driver";
		this.DB_URL = "jdbc:mysql://localhost/transport";
		this.USER = "root";
		this.PASS = "";
		
		try {
			Class.forName(JDBC_DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		// System.out.println("Connecting to database...");
		try {
			connection = DriverManager.getConnection(DB_URL, USER, PASS);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
 
	public Entrepot affectEntrepot(String v1, String v2){
		
		
		
		return null;
		
	}
	public int creerPersonne(String nom,String adresse) throws SQLException{
		Client f= null;
		String reqSql = "insert into Personne (NOM, ADRESSE) values (?,?)";
		PreparedStatement st=null;
		ResultSet res = null;
		int id=0;
		try{
			st = connection.prepareStatement(reqSql,PreparedStatement.RETURN_GENERATED_KEYS);
			st.setString(1, nom);
			st.setString(2, adresse);
			st.executeUpdate();
			res = st.getGeneratedKeys();
			if (res != null && res.next()) {
			    id = res.getInt(1);
			}
			
			
			return id;
		} catch ( SQLException e){
			 new Exception ("pb d'ajout de client dans la BD");
		}
		return id;
	}
	
	public int creerExpedition(int c, int p, ArrayList<Colis> colis, Date d1) throws SQLException
	{
		int id = 0;
		String reqSql = "insert into Expedition (ID_ENTREPOT, ID_PERSONNE, CLI_ID_PERSONNE,DATE_LIVRAISON) values (?,?,?,?)";
		PreparedStatement st=null;
		ResultSet res = null;
		try{
			st = connection.prepareStatement(reqSql,PreparedStatement.RETURN_GENERATED_KEYS);
			st.setInt(1,1);			
			st.setInt(2,p);
			st.setInt(3,c );
			st.setTimestamp(4, new Timestamp(d1.getTime()));
			st.executeUpdate();		
			System.out.println("ici");
			res = st.getGeneratedKeys();
			if (res != null && res.next()) {
			    id = res.getInt(1);
			}	
			for(int i =0;i<colis.size();i++){
				int col = creerColis(colis.get(i),id);
			}
			
			return id;
		} catch ( SQLException e){
			 new Exception ("pb d'ajout de client dans la BD");
		}
		return id;
	}
	
	public int creerColis(Colis c, int idExpedition) throws SQLException
	{
		String reqSql="insert INTO COLIS (ID_EXPEDITION,ID_TARIFICATION, POIDS,MONTANT) VALUES (?,?,?,?)";
		int id = 0;
		ResultSet res = null;
		try{
			PreparedStatement st = connection.prepareStatement(reqSql);
			st.setInt(1, idExpedition);
			st.setInt(2, 2);
			st.setFloat(3, c.getPoids());
			st.setFloat(4, c.getMontant());
			st.executeUpdate();
			
			res = st.getGeneratedKeys();
			if (res != null && res.next()) {
			    id = res.getInt(1);
			}
			
			
			return id;
		} catch ( SQLException e){
			 new Exception ("pb d'ajout de client dans la BD");
		}
		return id;
	}
	
	public Client creerClient(String nom,String adresse) throws SQLException{
		Client f= null;
		String reqSql = "insert into Client (ID_PERSONNE, NOM, ADRESSE) values (?,?,?)";
		PreparedStatement st=null;
		ResultSet res = null;
		int id=0;
		PreparedStatement state=null;
		try{
			state = connection.prepareStatement(reqSql,PreparedStatement.RETURN_GENERATED_KEYS);
			state.setInt(1, creerPersonne(nom,adresse));
			
			state.setString(2, nom);
			state.setString(3, adresse);
			System.out.println("ici");
			state.executeUpdate();
			
			res = state.getGeneratedKeys();
			if (res != null && res.next()) {
			    id = res.getInt(1);
			}
			f = new Client (id,nom,adresse);
			
			return f;
		} catch ( SQLException e){
			 new Exception ("pb d'ajout de client dans la BD");
		}
		return f;
	}
	public Personne rechercherPersonne(int id) throws SQLException
	{
		Personne c = null;
		ResultSet res = null;
		String request = "Select * from Personne where ID_PERSONNE = " + id;
		stmt = (Statement) connection.createStatement();
		res = stmt.executeQuery(request);
		while (res.next()) {
			c = new Personne (
					res.getInt("ID_PERSONNE"),
					res.getString("NOM"),
					res.getString("ADRESSE")
					);			
		}
		
		return (c);
	}
	public int rechercherPersonne(String p) throws SQLException
	{
		Personne c = null;
	int  t=0;
		ResultSet res = null;
		String request = "Select * from Personne where NOM = " + p;
		stmt = (Statement) connection.createStatement();
		res = stmt.executeQuery(request);
		while (res.next()) {
			
				t=	res.getInt("ID_PERSONNE");
		}
		return t;
	}
	
	public int creerDestinataire(String nom,String adresse) throws SQLException{
		String reqSql="insert INTO personne	 (NOM, ADRESSE) VALUES (?,?)";
		int n = 0;
		try{
			connection = DriverManager.getConnection("jdbc:mysql://localhost/transport", "root", "");
			PreparedStatement st = connection.prepareStatement(reqSql);
		st.setString(1, nom);
		st.setString(2, adresse);
		n =st.executeUpdate();
		
	}catch ( SQLException e){
		 new Exception ("Impossible de recuperer les données d'expedition");
	}finally {

		if (stmt != null) {
				stmt.close();
						}

		if (connection != null) {
			connection.close();
		}

	}
		return n;
	}
	
	public ArrayList<Client> rechercherClientAll() throws SQLException
	{
		ArrayList<Client> lsClient = new ArrayList<Client>();
		ResultSet res = null;
		String request = "Select * from CLIENT";
		stmt = (Statement) connection.createStatement();
		res = stmt.executeQuery(request);
		while (res.next()) {
			Client c = new Client (
					res.getInt("ID_PERSONNE"),
					res.getString("NOM"),
					res.getString("ADRESSE")
					);
			lsClient.add(c);
		}
		
		return (lsClient);
	}
	
	public Client rechercherClient(int id) throws SQLException
	{
		Client c = null;
		ResultSet res = null;
		String request = "Select * from CLIENT where ID_PERSONNE = " + id;
		stmt = (Statement) connection.createStatement();
		res = stmt.executeQuery(request);
		while (res.next()) {
			c = new Client (
					res.getInt("ID_PERSONNE"),
					res.getString("NOM"),
					res.getString("ADRESSE")
					);			
		}
		
		return (c);
	}
	
	public Fidele rendreClientFidele(Client oc){
		Fidele f= null;
		String reqSql = "insert into Fidele (ID_PERSONNE, NOM, ADRESSE, POINTSFIDELITE) values (?,?,?,?)";
		PreparedStatement st=null;
		try{
			Client c = rechercherClient(oc.getNumero());
			st = connection.prepareStatement(reqSql);
			st.setInt(1, c.getNumero());
			st.setString(2, c.getNom());
			st.setString(3, c.getAdresse());
			st.setInt(4, 0);
			f= new Fidele(c.getNumero(),c.getNom(),c.getAdresse());
			st.executeUpdate();
			return f;
		} catch ( SQLException e){
			 new Exception ("pb d'ajout de client dans la BD");
		}
		return null;
	}
	public void attExpeditionMission(int exp, int numMission){
		//String reqSql = "insert into Expedition (ID_MISSION) values (?) where ID_EXPEDITION=?";
		String reqSql = "update Expedition set ID_MISSION = ? where ID_EXPEDITION = ?";
		PreparedStatement st=null;
		try{
			st = connection.prepareStatement(reqSql);
			st.setInt(1,numMission );
			st.setInt(2, exp);
			st.executeUpdate();
		} catch ( SQLException e){
			 new Exception ("pb d'ajout de client dans la BD");
		}
	}
	public Mission creerMission(int numeroEntrepot, int emp, Date d1, int[] tab) throws SQLException {
		long id =0;
		Mission mission =null;
		Statement stat = null;
		String request = "insert into Mission (ID_ENTREPOT, DATE_DEPART) VALUES (?,?)";
		
		PreparedStatement st=null;
		ResultSet res = null;
		ResultSet res1 = null;
		ArrayList<Expedition> list =null;
		Expedition e =null;
		
		try{
			st = connection.prepareStatement(request, PreparedStatement.RETURN_GENERATED_KEYS);
			st.setInt(1, numeroEntrepot);
			st.setTimestamp(2,new Timestamp(d1.getTime()));
			st.executeUpdate();
			res = st.getGeneratedKeys();
			if (res != null && res.next()) {
			    id = res.getInt(1);
			}
			for(int i=0;i<tab.length;i++){
				 attExpeditionMission( tab[i], (int)id);
				}
			
			String req = "Select * from expedition where ID_MISSION = " + id;
			stmt = (Statement) connection.createStatement();
			res1 = stmt.executeQuery(req);
			while (res1.next()) {
				e = new Expedition (
						rechercherClient(res1.getInt("CLI_ID_PERSONNE")),
						rechercherPersonne(res1.getInt("ID_PERSONNE")),
						rechercherColisExpedition(res1.getInt("ID_EXPEDITION")));
				list.add(e);
			}
			mission = new Mission(d1,(int)id,list);
			System.out.println(mission.getNumero());
			return mission;
			
			
		} catch ( SQLException en){
			 new Exception ("pb d'ajout de client dans la BD");
		}
		return mission;
	}
			
	public float consulterChiffreAffaire(Date d, Date d2){
		//String reqSql ="select PRIX_TOTAL FROM expedition WHERE DATE_LIVRAISON>=? AND DATE_LIVRAISON<=?";
		float ca =0;
		ResultSet res1 = null;
		try{
		//	PreparedStatement st= connection.prepareStatement(reqSql);
			
			String req = "Select * from expedition WHERE DATE_LIVRAISON>="+new Timestamp(d.getTime())+" AND DATE_LIVRAISON<="+new Timestamp(d2.getTime());
			stmt = (Statement) connection.createStatement();
			res1 = stmt.executeQuery(req);
			while (res1.next()) {
				ca+=res1.getFloat("PRIX_TOTAL");
			}
			return ca;
		}catch (SQLException e){
			
			new Exception ("pb de recuperation dans la BD");
		}
		
		return ca;
		
		
	}
	
	private float calculerDistance (String villeA, String villeB)
	{
		float distance, lon1 = 0, lon2 = 0, lat1 = 0, lat2 = 0;
		
		String request = "Select longitude, latitude from cities where name = ?";
		
		try{
			PreparedStatement st = connection.prepareStatement(request);
			st.setString(1, villeA);
			ResultSet resA = st.executeQuery();
			lon1 = resA.getFloat("longitude");
			lat1 = resA.getFloat("latitude");
			
			st.setString(1, villeB);
			ResultSet resB = st.executeQuery();
			lon2 = resB.getFloat("longitude");
			lat2 = resB.getFloat("latitude");
		} catch ( SQLException e){
			 new Exception ("Impossible de recuperer les coordonées");
		}

		float theta = lon1 - lon2;
		distance = (float) (Math.sin(Math.toRadians(lat1)) * Math.sin(Math.toRadians(lat2)) +  Math.cos(Math.toRadians(lat1)) * Math.cos(Math.toRadians(lat2)) * Math.cos(Math.toRadians(theta)));
		distance = (float) Math.acos(distance);
		distance = (float) Math.toDegrees(distance);
		distance = (float) (distance * 60 * 1.1515);
		distance = (float) (distance * 1.609344);
		
		return (distance);
	}
	
	
	
	public Personne rechercherDestinataire(int id)
	{
		Personne d = null;
		String request = "Select * from PERSONNE where ID_PERSONNE = ?";

		try{
			PreparedStatement st = connection.prepareStatement(request);
			st.setLong(1, id);
			ResultSet res = st.executeQuery();
			d = new Personne (
					res.getString("NOM"),
					res.getString("ADRESSE")
					);
		} catch ( SQLException e){
			 new Exception ("Impossible de recuperer les informations du client");
		}
		
		return (d);
	}

	public Entrepot rechercherEntrepot(int id)
	{
		Entrepot en = null;
		String request = "Select * from ENTREPOT where ID_ENTREPOT = ?";

		try{
			PreparedStatement st = connection.prepareStatement(request);
			st.setLong(1, id);
			ResultSet res = st.executeQuery();
			en = new Entrepot (
					res.getInt("ID_ENTREPOT")
					);
		} catch ( SQLException e){
			 new Exception ("Impossible de recuperer les informations du client");
		}
		
		return (en);
	}

	public ArrayList<Colis> rechercherColisExpedition(int idExpedition)
	{
		ArrayList<Colis> lsColis = new ArrayList<Colis>();
		String request = "Select * from COLIS where ID_EXPEDITION = ?";

		try{
			PreparedStatement st = connection.prepareStatement(request);
			st.setLong(1, idExpedition);
			ResultSet res = st.executeQuery();
			
			while (!res.isLast())
			{
				Colis c = new Colis(
						res.getInt("ID_COLIS"),
						res.getFloat("POIDS"),
						res.getFloat("MONTANT")
						);
				lsColis.add(c);
				res.next();
			}
		} catch ( SQLException e){
			 new Exception ("Impossible de recuperer les informations des colis");
		}
		
		return (lsColis);
	}
	
	public Expedition rechercherExpedition(int numero) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException
	{
		Expedition ex = null;
		ResultSet res = null;
		String request = "Select * from EXPEDITION where ID_EXPEDITION = 3";
		stmt = (Statement) connection.createStatement();
		res = stmt.executeQuery(request);
		while (res.next()) {
			Client c = rechercherClient(res.getInt("CLI_ID_PERSONNE"));
			Personne d = rechercherDestinataire(res.getInt("ID_PERSONNE"));
			Entrepot en = rechercherEntrepot(res.getInt("ID_ENTREPOT")); 
			ArrayList<Colis> lsColis = rechercherColisExpedition(numero);
			ex = new Expedition(c, d, lsColis, en);
		}
		
		return (ex);
	}
	public ArrayList<Expedition> rechercherExpeditionSurPeriode(int numeroClient, Date d1, Date d2){
		ArrayList<Expedition> ls= new ArrayList<Expedition>();
		String request = "Select * from EXPEDITION where CLI_ID_PERSONNE = ? AND DATE_LIVRAISON>=? AND DATE_LIVRAISON<=? ";
		PreparedStatement st= null;
		try{
			 st = connection.prepareStatement(request);
			st.setInt(1, numeroClient);
			 SimpleDateFormat format = new SimpleDateFormat("yyyy-mm-dd");
			 String form= format.format(d1);
			 java.sql.Date date = java.sql.Date.valueOf(form);
			st.setDate(2, date);
			st.setTimestamp(3,new Timestamp(d2.getTime()));
			
			ResultSet res = st.executeQuery();
			System.out.println("ici");
			while(!res.isLast()){
				Expedition ex = null;
				Client c = rechercherClient(res.getInt("CLI_ID_PERSONNE"));
				Personne d = rechercherDestinataire(res.getInt("ID_PERSONNE"));
				Entrepot en = rechercherEntrepot(res.getInt("ID_ENTREPOT")); 
				float prix = res.getFloat("PRIX_TOTAL");
				ArrayList<Colis> lsColis = rechercherColisExpedition(res.getInt("ID_EXPEDITION"));
				ex = new Expedition(c, d, lsColis, en);
				ex.setPrixTotal(prix);
				ls.add(ex);
			}
		}catch ( SQLException e){
			 new Exception ("Impossible de recuperer les données d'expedition");
		}
		return ls;
	}
	
	
}
